/*
 * @(#)MtmEventMessageLog.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.framework.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import com.dcivision.calendar.CalendarUtility;
import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;
import com.dcivision.framework.bean.MtmEventMessageLog;
import com.dcivision.framework.web.AbstractSearchForm;
import com.dcivision.framework.web.ListEventMessageForm;

/**
  MtmEventMessageLogDAObject.java

  This class is the data access bean for table "MTM_EVENT_MESSAGE_LOG".

  @author      Anson mo
  @company     DCIVision Limited
  @creation date   09/09/2005
  @version     $Revision: 1.5 $
*/

public class MtmEventMessageLogDAObject extends AbstractDAObject {

  public static final String REVISION = "$Revision: 1.5 $";

  public static final String TABLE_NAME = "MTM_EVENT_MESSAGE_LOG";

  public MtmEventMessageLogDAObject(SessionContainer sessionContainer, Connection dbConn) {
    super(sessionContainer, dbConn);
    this.isSoftDeleteEnabled = false;
  }

  protected void initDBSetting() {
    this.baseTableName = TABLE_NAME;
    this.vecDBColumn.add("ID");
    this.vecDBColumn.add("MESSAGE_CODE_ID");
    this.vecDBColumn.add("MESSAGE");
    this.vecDBColumn.add("SOURCE_FUNCTION_CODE");
    this.vecDBColumn.add("ERROR_DETAIL");
    this.vecDBColumn.add("ACTION_TAKER_ID");
    this.vecDBColumn.add("SERVER_NAME");
    this.vecDBColumn.add("RECORD_STATUS");
    this.vecDBColumn.add("UPDATE_COUNT");
    this.vecDBColumn.add("CREATOR_ID");
    this.vecDBColumn.add("CREATE_DATE");
    this.vecDBColumn.add("UPDATER_ID");
    this.vecDBColumn.add("UPDATE_DATE");
  }

  protected synchronized AbstractBaseObject getByID(Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.MESSAGE_CODE_ID, A.MESSAGE, A.SOURCE_FUNCTION_CODE, A.ERROR_DETAIL, A.ACTION_TAKER_ID, A.SERVER_NAME, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   MTM_EVENT_MESSAGE_LOG A ");
        sqlStat.append("WHERE  A.ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, id);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          MtmEventMessageLog tmpMtmEventMessageLog = new MtmEventMessageLog();
          tmpMtmEventMessageLog.setID(getResultSetInteger(rs, "ID"));
          tmpMtmEventMessageLog.setMessageCodeID(getResultSetInteger(rs, "MESSAGE_CODE_ID"));
          tmpMtmEventMessageLog.setMessage(getResultSetString(rs, "MESSAGE"));
          tmpMtmEventMessageLog.setSourceFunctionCode(getResultSetString(rs, "SOURCE_FUNCTION_CODE"));
          tmpMtmEventMessageLog.setErrorDetail(getResultSetString(rs, "ERROR_DETAIL"));
          tmpMtmEventMessageLog.setActionTakerID(getResultSetInteger(rs, "ACTION_TAKER_ID"));
          tmpMtmEventMessageLog.setServerName(getResultSetString(rs, "SERVER_NAME"));
          tmpMtmEventMessageLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpMtmEventMessageLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpMtmEventMessageLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpMtmEventMessageLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpMtmEventMessageLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpMtmEventMessageLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpMtmEventMessageLog.setCreatorName(UserInfoFactory.getUserFullName(tmpMtmEventMessageLog.getCreatorID()));
          tmpMtmEventMessageLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpMtmEventMessageLog.getUpdaterID()));
          return(tmpMtmEventMessageLog);
        } else {
          throw new ApplicationException(ErrorConstant.DB_RECORD_NOT_FOUND_ERROR);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized List getList(AbstractSearchForm searchForm) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.MESSAGE_CODE_ID, A.MESSAGE, A.SOURCE_FUNCTION_CODE, A.ERROR_DETAIL, A.ACTION_TAKER_ID, A.SERVER_NAME, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   MTM_EVENT_MESSAGE_LOG A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");

        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }

        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());

        // Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }

        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, 2, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }

        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, 2, searchKeyword);
        }
        rs = preStat.executeQuery();

        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt<pageSize) {
          MtmEventMessageLog tmpMtmEventMessageLog = new MtmEventMessageLog();
          tmpMtmEventMessageLog.setID(getResultSetInteger(rs, "ID"));
          tmpMtmEventMessageLog.setMessageCodeID(getResultSetInteger(rs, "MESSAGE_CODE_ID"));
          tmpMtmEventMessageLog.setMessage(getResultSetString(rs, "MESSAGE"));
          tmpMtmEventMessageLog.setSourceFunctionCode(getResultSetString(rs, "SOURCE_FUNCTION_CODE"));
          tmpMtmEventMessageLog.setErrorDetail(getResultSetString(rs, "ERROR_DETAIL"));
          tmpMtmEventMessageLog.setActionTakerID(getResultSetInteger(rs, "ACTION_TAKER_ID"));
          tmpMtmEventMessageLog.setServerName(getResultSetString(rs, "SERVER_NAME"));
          tmpMtmEventMessageLog.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpMtmEventMessageLog.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpMtmEventMessageLog.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpMtmEventMessageLog.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpMtmEventMessageLog.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpMtmEventMessageLog.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpMtmEventMessageLog.setCreatorName(UserInfoFactory.getUserFullName(tmpMtmEventMessageLog.getCreatorID()));
          tmpMtmEventMessageLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpMtmEventMessageLog.getUpdaterID()));
          tmpMtmEventMessageLog.setRecordCount(totalNumOfRecord);
          tmpMtmEventMessageLog.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpMtmEventMessageLog);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }

  public synchronized List getList() throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.MESSAGE_CODE_ID, A.MESSAGE, A.SOURCE_FUNCTION_CODE, A.ERROR_DETAIL, A.ACTION_TAKER_ID, A.SERVER_NAME, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.EVENT_TYPE ,B.MESSAGE_PATTERN ,B.ERROR_LABEL_KEY ,B.MESSAGE_CODE ");
        sqlStat.append("FROM   MTM_EVENT_MESSAGE_LOG A, EVENT_MESSAGE_CODE B ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? AND A.MESSAGE_CODE_ID = B.ID ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          MtmEventMessageLog tmpMtmEventMessageLog = new MtmEventMessageLog();
          tmpMtmEventMessageLog.setID(getResultSetInteger(rs, "A.ID"));
          tmpMtmEventMessageLog.setMessageCodeID(getResultSetInteger(rs, "A.MESSAGE_CODE_ID"));
          tmpMtmEventMessageLog.setMessage(getResultSetString(rs, "A.MESSAGE"));
          tmpMtmEventMessageLog.setSourceFunctionCode(getResultSetString(rs, "A.SOURCE_FUNCTION_CODE"));
          tmpMtmEventMessageLog.setErrorDetail(getResultSetString(rs, "A.ERROR_DETAIL"));
          tmpMtmEventMessageLog.setActionTakerID(getResultSetInteger(rs, "A.ACTION_TAKER_ID"));
          tmpMtmEventMessageLog.setActionTakerName(UserInfoFactory.getUserFullName(tmpMtmEventMessageLog.getCreatorID()));
          tmpMtmEventMessageLog.setServerName(getResultSetString(rs, "A.SERVER_NAME"));
          tmpMtmEventMessageLog.setRecordStatus(getResultSetString(rs, "A.RECORD_STATUS"));
          tmpMtmEventMessageLog.setUpdateCount(getResultSetInteger(rs, "A.UPDATE_COUNT"));
          tmpMtmEventMessageLog.setCreatorID(getResultSetInteger(rs, "A.CREATOR_ID"));
          tmpMtmEventMessageLog.setCreateDate(getResultSetTimestamp(rs, "A.CREATE_DATE"));
          tmpMtmEventMessageLog.setUpdaterID(getResultSetInteger(rs, "A.UPDATER_ID"));
          tmpMtmEventMessageLog.setUpdateDate(getResultSetTimestamp(rs, "A.UPDATE_DATE"));
          tmpMtmEventMessageLog.setCreatorName(UserInfoFactory.getUserFullName(tmpMtmEventMessageLog.getCreatorID()));
          tmpMtmEventMessageLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpMtmEventMessageLog.getUpdaterID()));
          tmpMtmEventMessageLog.setEventType(getResultSetString(rs,"B.EVENT_TYPE"));
          tmpMtmEventMessageLog.setMessagePattern(getResultSetString(rs,"B.MESSAGE_PATTERN"));
          tmpMtmEventMessageLog.setMsg_Code(getResultSetString(rs,"B.ERROR_LABEL_KEY"));
          tmpMtmEventMessageLog.setMessageCode(getResultSetString(rs,"B.MESSAGE_CODE"));
          result.add(tmpMtmEventMessageLog);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected void validateInsert(AbstractBaseObject obj) throws ApplicationException {
  }

  protected void validateUpdate(AbstractBaseObject obj) throws ApplicationException {
  }

  protected void validateDelete(AbstractBaseObject obj) throws ApplicationException {
  }

  protected synchronized AbstractBaseObject insert(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    MtmEventMessageLog tmpMtmEventMessageLog = (MtmEventMessageLog)((MtmEventMessageLog)obj).clone();
    Integer userRecord = null;
    if (sessionContainer.getUserRecordID() != null){
      userRecord = sessionContainer.getUserRecordID();
    } else {
      userRecord = tmpMtmEventMessageLog.getActionTakerID();
    }

    synchronized(dbConn) {
      try {
        Integer nextID = getNextPrimaryID();
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("INSERT ");
        sqlStat.append("INTO   MTM_EVENT_MESSAGE_LOG(ID, MESSAGE_CODE_ID, MESSAGE, SOURCE_FUNCTION_CODE, ERROR_DETAIL, ACTION_TAKER_ID, SERVER_NAME, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) ");
        sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, nextID);
        setPrepareStatement(preStat, 2, tmpMtmEventMessageLog.getMessageCodeID());
        setPrepareStatement(preStat, 3, tmpMtmEventMessageLog.getMessage());
        setPrepareStatement(preStat, 4, tmpMtmEventMessageLog.getSourceFunctionCode());
        setPrepareStatement(preStat, 5, tmpMtmEventMessageLog.getErrorDetail());
        setPrepareStatement(preStat, 6, tmpMtmEventMessageLog.getActionTakerID());
        setPrepareStatement(preStat, 7, tmpMtmEventMessageLog.getServerName());
        setPrepareStatement(preStat, 8, GlobalConstant.RECORD_STATUS_ACTIVE);
        setPrepareStatement(preStat, 9, new Integer(0));
        setPrepareStatement(preStat, 10, userRecord);
        setPrepareStatement(preStat, 11, currTime);
        setPrepareStatement(preStat, 12, userRecord);
        setPrepareStatement(preStat, 13, currTime);
        preStat.executeUpdate();
        tmpMtmEventMessageLog.setID(nextID);
        tmpMtmEventMessageLog.setCreatorID(userRecord);
        tmpMtmEventMessageLog.setCreateDate(currTime);
        tmpMtmEventMessageLog.setUpdaterID(userRecord);
        tmpMtmEventMessageLog.setUpdateDate(currTime);
        tmpMtmEventMessageLog.setUpdateCount(new Integer(0));
        tmpMtmEventMessageLog.setCreatorName(UserInfoFactory.getUserFullName(tmpMtmEventMessageLog.getCreatorID()));
        tmpMtmEventMessageLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpMtmEventMessageLog.getUpdaterID()));
        return(tmpMtmEventMessageLog);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized AbstractBaseObject update(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    MtmEventMessageLog tmpMtmEventMessageLog = (MtmEventMessageLog)((MtmEventMessageLog)obj).clone();

    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("UPDATE MTM_EVENT_MESSAGE_LOG ");
        sqlStat.append("SET  MESSAGE_CODE_ID=?, MESSAGE=?, SOURCE_FUNCTION_CODE=?, ERROR_DETAIL=?, ACTION_TAKER_ID=?, SERVER_NAME=?, UPDATE_COUNT=?, UPDATER_ID=?, UPDATE_DATE=? ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpMtmEventMessageLog.getMessageCodeID());
        setPrepareStatement(preStat, 2, tmpMtmEventMessageLog.getMessage());
        setPrepareStatement(preStat, 3, tmpMtmEventMessageLog.getSourceFunctionCode());
        setPrepareStatement(preStat, 4, tmpMtmEventMessageLog.getErrorDetail());
        setPrepareStatement(preStat, 5, tmpMtmEventMessageLog.getActionTakerID());
        setPrepareStatement(preStat, 6, tmpMtmEventMessageLog.getServerName());
        setPrepareStatement(preStat, 7, new Integer(tmpMtmEventMessageLog.getUpdateCount().intValue() + 1));
        setPrepareStatement(preStat, 8, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 9, currTime);
        setPrepareStatement(preStat, 10, tmpMtmEventMessageLog.getID());
        setPrepareStatement(preStat, 11, tmpMtmEventMessageLog.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          tmpMtmEventMessageLog.setUpdaterID(sessionContainer.getUserRecordID());
          tmpMtmEventMessageLog.setUpdateDate(currTime);
          tmpMtmEventMessageLog.setUpdateCount(new Integer(tmpMtmEventMessageLog.getUpdateCount().intValue() + 1));
          tmpMtmEventMessageLog.setCreatorName(UserInfoFactory.getUserFullName(tmpMtmEventMessageLog.getCreatorID()));
          tmpMtmEventMessageLog.setUpdaterName(UserInfoFactory.getUserFullName(tmpMtmEventMessageLog.getUpdaterID()));
          return(tmpMtmEventMessageLog);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized AbstractBaseObject delete(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    MtmEventMessageLog tmpMtmEventMessageLog = (MtmEventMessageLog)((MtmEventMessageLog)obj).clone();

    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM   MTM_EVENT_MESSAGE_LOG ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpMtmEventMessageLog.getID());
        setPrepareStatement(preStat, 2, tmpMtmEventMessageLog.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          return(tmpMtmEventMessageLog);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }

  protected synchronized void auditTrail(String opMode, AbstractBaseObject obj) throws ApplicationException {
    Vector oldValues = new Vector();
    Vector newValues = new Vector();
    MtmEventMessageLog tmpMtmEventMessageLog = (MtmEventMessageLog)this.oldValue;

    if (tmpMtmEventMessageLog != null) {
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getID()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getMessageCodeID()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getMessage()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getSourceFunctionCode()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getErrorDetail()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getActionTakerID()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getServerName()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getRecordStatus()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getUpdateCount()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getCreatorID()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getCreateDate()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getUpdaterID()));
      oldValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getUpdateDate()));
    }

    tmpMtmEventMessageLog = (MtmEventMessageLog)obj;
    if (tmpMtmEventMessageLog != null) {
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getID()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getMessageCodeID()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getMessage()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getSourceFunctionCode()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getErrorDetail()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getActionTakerID()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getServerName()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getRecordStatus()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getUpdateCount()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getCreatorID()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getCreateDate()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getUpdaterID()));
      newValues.add(toAuditTrailValue(tmpMtmEventMessageLog.getUpdateDate()));
    }

    auditTrailBase(opMode, oldValues, newValues);
  }
  
  public synchronized List getMtmEventList(AbstractSearchForm searchForm) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());
    ListEventMessageForm eventMessageForm = (ListEventMessageForm) searchForm;
//    Timestamp currTime = Utility.getCurrentTimestamp();
  //  Timestamp oneDayBeforeCurrTime = Utility.addDay(currTime, -1);

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.MESSAGE_CODE_ID, A.MESSAGE, A.ACTION_TAKER_ID, A.SERVER_NAME, A.SOURCE_FUNCTION_CODE, A.RECORD_STATUS, A.CREATOR_ID, A.CREATE_DATE, A.UPDATE_COUNT, A.UPDATER_ID, A.UPDATE_DATE, B.EVENT_TYPE ,B.MESSAGE_PATTERN ,B.ERROR_LABEL_KEY ,B.MESSAGE_CODE ,C.LOGIN_NAME ");
        sqlStat.append("FROM   MTM_EVENT_MESSAGE_LOG A ,EVENT_MESSAGE_CODE B ,USER_RECORD C ");
        sqlStat.append("WHERE  A.MESSAGE_CODE_ID = B.ID AND A.ACTION_TAKER_ID=C.ID AND A.RECORD_STATUS = ? ");
        if (!Utility.isEmpty(eventMessageForm.getUserRecordID())) {
          sqlStat.append("AND A.CREATOR_ID = ? ");
        }
//        sqlStat.append("       AND (((A.EFFECTIVE_START_DATE IS NULL OR A.EFFECTIVE_START_DATE <= ?) ");
//        sqlStat.append("       AND (A.EFFECTIVE_END_DATE IS NULL OR A.EFFECTIVE_END_DATE > ?)) ");
//        sqlStat.append("       OR A.OWNER_ID = ?) ");


        // Form the WHERE clause for filtering.
        /*if (searchForm.isSearchable()) {
          String searchField = searchForm.getBasicSearchField();
          if (searchField.indexOf(".") < 0) {
            searchField = "A." + searchField;
          }
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }*/
        
        if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          sqlStat.append("AND (A.UPDATE_DATE >= ? AND A.UPDATE_DATE <= ? ) ");
        }
        else if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          sqlStat.append("AND (A.UPDATE_DATE >= ? ) ");
        }
        else if (Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          sqlStat.append("AND (A.UPDATE_DATE <= ?  ) ");
        }

        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }

        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());

        // Form the ORDER clause for sorting.
//        sqlStat.append("ORDER BY A.CREATE_DATE DESC  ");
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }

        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());        
//        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
//        this.setPrepareStatement(preStatCnt, 2, DmsDocument.DOCUMENT_TYPE);
//        this.setPrepareStatement(preStatCnt, 3, DmsDocument.COMPOUND_DOC_TYPE);
//        this.setPrepareStatement(preStatCnt, 4, DmsDocument.PAPER_DOC_TYPE);  
//        this.setPrepareStatement(preStatCnt, 5, docSearchForm.getRootID());
//        this.setPrepareStatement(preStatCnt, 6, docSearchForm.getParentID());
//        this.setPrepareStatement(preStatCnt, 7, currTime);
//
//
//        this.setPrepareStatement(preStatCnt, 8, oneDayBeforeCurrTime);
//        this.setPrepareStatement(preStatCnt, 9, this.sessionContainer.getUserRecordID());
        int i = 0;
        this.setPrepareStatement(preStatCnt,++i,GlobalConstant.RECORD_STATUS_ACTIVE);
        if (!Utility.isEmpty(eventMessageForm.getUserRecordID())) {
          this.setPrepareStatement(preStatCnt,++i,eventMessageForm.getUserRecordID());
        }        
        if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          this.setPrepareStatement(preStatCnt, ++i, eventMessageForm.getAccessDateFrom());
          this.setPrepareStatement(preStatCnt, ++i, eventMessageForm.getAccessDateTo());
        }
        else if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          this.setPrepareStatement(preStatCnt, ++i, eventMessageForm.getAccessDateFrom());
        }
        else if (Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          this.setPrepareStatement(preStatCnt, ++i, eventMessageForm.getAccessDateTo());
        }

        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, ++i, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try {
          rsCnt.close();
        } catch (Exception ignore) {} finally {
          rsCnt = null;
        }
        try {
          preStatCnt.close();
        } catch (Exception ignore) {} finally {
          preStatCnt = null;
        }

        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
//        this.setPrepareStatement(preStat, 2, DmsDocument.DOCUMENT_TYPE);
//        this.setPrepareStatement(preStat, 3, DmsDocument.COMPOUND_DOC_TYPE);
//        this.setPrepareStatement(preStat, 4, DmsDocument.PAPER_DOC_TYPE);
//        this.setPrepareStatement(preStat, 5, docSearchForm.getRootID());
//        this.setPrepareStatement(preStat, 6, docSearchForm.getParentID());
//        this.setPrepareStatement(preStat, 7, currTime);
//
//
//        this.setPrepareStatement(preStat, 8, oneDayBeforeCurrTime);
//        this.setPrepareStatement(preStat, 9, this.sessionContainer.getUserRecordID());
        i = 0;
        this.setPrepareStatement(preStat,++i,GlobalConstant.RECORD_STATUS_ACTIVE);
        if (!Utility.isEmpty(eventMessageForm.getUserRecordID())) {
          this.setPrepareStatement(preStat,++i,eventMessageForm.getUserRecordID());
        } 
        if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateFrom());
          this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateTo());
        }
        else if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateFrom());
        }
        else if (Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateTo());
        }

        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, ++i, searchKeyword);
        }
        rs = preStat.executeQuery();
        this.positionCursor(rs, startOffset, pageSize);

        while (rs.next() && rowLoopCnt < pageSize) {
          MtmEventMessageLog mtmEventMessageLog = new MtmEventMessageLog();
//          String messageCode = this.getResultSetString(rs,"MESSAGE_CODE");
//          String source = getSource(messageCode);
          
          mtmEventMessageLog.setID(this.getResultSetInteger(rs,"ID"));
          mtmEventMessageLog.setMessageCodeID(this.getResultSetInteger(rs,"MESSAGE_CODE_ID"));
          mtmEventMessageLog.setMessage(this.getResultSetString(rs,"MESSAGE"));
          mtmEventMessageLog.setActionTakerID(this.getResultSetInteger(rs,"ACTION_TAKER_ID"));
          mtmEventMessageLog.setServerName(this.getResultSetString(rs,"SERVER_NAME"));
          mtmEventMessageLog.setCreatorID(this.getResultSetInteger(rs,"CREATOR_ID"));
          mtmEventMessageLog.setCreateDate(this.getResultSetTimestamp(rs,"CREATE_DATE"));
          mtmEventMessageLog.setRecordStatus(this.getResultSetString(rs,"RECORD_STATUS"));
          mtmEventMessageLog.setUpdateCount(this.getResultSetInteger(rs,"A.UPDATE_COUNT"));
          mtmEventMessageLog.setUpdaterID(this.getResultSetInteger(rs,"A.UPDATER_ID"));
          mtmEventMessageLog.setUpdateDate(this.getResultSetTimestamp(rs,"A.UPDATE_DATE"));
          mtmEventMessageLog.setEventType(this.getResultSetString(rs,"EVENT_TYPE"));
          mtmEventMessageLog.setMessagePattern(this.getResultSetString(rs,"MESSAGE_PATTERN"));
          mtmEventMessageLog.setMsg_Code(this.getResultSetString(rs,"ERROR_LABEL_KEY"));
          mtmEventMessageLog.setMessageCode(this.getResultSetString(rs,"MESSAGE_CODE"));
          mtmEventMessageLog.setSourceFunctionCode(this.getResultSetString(rs,"A.SOURCE_FUNCTION_CODE"));
          mtmEventMessageLog.setActionTakerName(this.getResultSetString(rs,"C.LOGIN_NAME"));
          mtmEventMessageLog.setErrorCode(this.getResultSetString(rs,"B.MESSAGE_CODE"));
          
//          log.debug("-------------source: " + source);
//          mtmEventMessageLog.setSource(source);
//          Date date = this.getResultSetDate(rs,"A.CREATE_DATE");
//          TextUtility.getDateTimeFormat();
          TextUtility.formatDate(mtmEventMessageLog.getUpdateDate(),CalendarUtility.TIME_FORMFAT_A);
          mtmEventMessageLog.setDate(TextUtility.formatDate(mtmEventMessageLog.getUpdateDate(),"yyyy-MM-dd"));
          mtmEventMessageLog.setTime(TextUtility.formatDate(mtmEventMessageLog.getUpdateDate(),"hh:mm:ss a"));
          
//          SELECT A.ID, A.MESSAGE_COD_ID, A.MESSAGE, A.ACTION_TAKER_ID, A.SERVER_NAME, 
//          A.CREATEOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ,B.EVENT_TYPE ,B.MESSAGE_PATTERN ,
//          B.MSG_CODE ,B.MESSAGE_CODE 
          
//          DmsDocument tmpDmsDocument = parseObjectFromResultSet(rs);
          mtmEventMessageLog.setRecordCount(totalNumOfRecord);
          mtmEventMessageLog.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(mtmEventMessageLog);
        }

        return (result);
//      } catch (ApplicationException appEx) {
//        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
        try {
          rsCnt.close();
        } catch (Exception ignore) {} finally {
          rsCnt = null;
        }
        try {
          preStatCnt.close();
        } catch (Exception ignore) {} finally {
          preStatCnt = null;
        }
      }
    }
  }
  
  public synchronized int deleteAll() throws ApplicationException {
    int total = 0;
    if (this.isSoftDeleteEnabled) {
      StringBuffer sqlStat = new StringBuffer("UPDATE MTM_EVENT_MESSAGE_LOG SET RECORD_STATUS='" + GlobalConstant.RECORD_STATUS_INACTIVE + "', UPDATE_DATE=?, UPDATER_ID=? ");

      PreparedStatement preStat = null;

      synchronized(dbConn) {
        try {   
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          this.setPrepareStatement(preStat, 1, Utility.getCurrentTimestamp());
          this.setPrepareStatement(preStat, 2, sessionContainer==null ? new Integer(0) : sessionContainer.getUserRecordID());
          total = preStat.executeUpdate();
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, e, e.toString());
        } finally {
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        }
      }
      return total;
    } else {
      StringBuffer sqlStat = new StringBuffer("DELETE FROM MTM_EVENT_MESSAGE_LOG WHERE RECORD_STATUS='" + GlobalConstant.RECORD_STATUS_ACTIVE + "'");
      
      PreparedStatement preStat = null;

      synchronized(dbConn) {
        try {          
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          total = preStat.executeUpdate();
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, e, e.toString());
        } finally {
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        }
      }
      return total;
    }
  }
  
  public synchronized int deleteAll(AbstractSearchForm searchForm) throws ApplicationException {
    ListEventMessageForm eventMessageForm = (ListEventMessageForm) searchForm;
    int totalNumOfRecord = 0;
    if (this.isSoftDeleteEnabled) {
      StringBuffer sqlStat = new StringBuffer("UPDATE MTM_EVENT_MESSAGE_LOG SET RECORD_STATUS='" + GlobalConstant.RECORD_STATUS_ACTIVE + "', UPDATE_DATE=?, UPDATER_ID=? ");
      
      if (!Utility.isEmpty(eventMessageForm.getUserRecordID())) {
        sqlStat.append("AND CREATOR_ID = ? ");
      }
      
      if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
        sqlStat.append("AND (UPDATE_DATE >= ? AND UPDATE_DATE <= ? ) ");
      }
      else if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
        sqlStat.append("AND (UPDATE_DATE >= ? ) ");
      }
      else if (Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
        sqlStat.append("AND (UPDATE_DATE <= ?  ) ");
      }

      PreparedStatement preStat = null;

      synchronized(dbConn) {
        try {
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          int i=0;
          this.setPrepareStatement(preStat, ++i, Utility.getCurrentTimestamp());
          this.setPrepareStatement(preStat, ++i, sessionContainer==null ? new Integer(0) : sessionContainer.getUserRecordID());
          
          if (!Utility.isEmpty(eventMessageForm.getUserRecordID())) {
            this.setPrepareStatement(preStat,++i,eventMessageForm.getUserRecordID());
          }        
          
          if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
            this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateFrom());
            this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateTo());
          }
          else if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
            this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateFrom());
          }
          else if (Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
            this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateTo());
          }
          totalNumOfRecord = preStat.executeUpdate();
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, e, e.toString());
        } finally {
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        }
      }
      
      return totalNumOfRecord;
    } else {
      StringBuffer sqlStat = new StringBuffer("DELETE FROM MTM_EVENT_MESSAGE_LOG WHERE RECORD_STATUS='" + GlobalConstant.RECORD_STATUS_ACTIVE + "' ");
      
      if (!Utility.isEmpty(eventMessageForm.getUserRecordID())) {
        sqlStat.append("AND CREATOR_ID = ? ");
      }
      
      if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
        sqlStat.append("AND (UPDATE_DATE >= ? AND UPDATE_DATE <= ? ) ");
      }
      else if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
        sqlStat.append("AND (UPDATE_DATE >= ? ) ");
      }
      else if (Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
        sqlStat.append("AND (UPDATE_DATE <= ?  ) ");
      }
      
      PreparedStatement preStat = null;

      synchronized(dbConn) {
        try {
          preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
          int i =0;
          if (!Utility.isEmpty(eventMessageForm.getUserRecordID())) {
            this.setPrepareStatement(preStat,++i,eventMessageForm.getUserRecordID());
          }        
          
          if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
            this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateFrom());
            this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateTo());
          }
          else if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
            this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateFrom());
          }
          else if (Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
            this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateTo());
          }
          totalNumOfRecord = preStat.executeUpdate();
        } catch (Exception e) {
          log.error(e, e);
          throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, e, e.toString());
        } finally {
          try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        }
      }
      return totalNumOfRecord;
    }
  }
  
  public synchronized List getListAll(AbstractSearchForm searchForm) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    ListEventMessageForm eventMessageForm = (ListEventMessageForm) searchForm;

    synchronized (dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.MESSAGE_CODE_ID, A.MESSAGE, A.ACTION_TAKER_ID, A.SERVER_NAME, A.SOURCE_FUNCTION_CODE, A.RECORD_STATUS, A.CREATOR_ID, A.CREATE_DATE, A.UPDATE_COUNT, A.UPDATER_ID, A.UPDATE_DATE,A.ERROR_DETAIL, B.EVENT_TYPE ,B.MESSAGE_PATTERN ,B.ERROR_LABEL_KEY ,B.MESSAGE_CODE ,C.LOGIN_NAME ");
        sqlStat.append("FROM   MTM_EVENT_MESSAGE_LOG A ,EVENT_MESSAGE_CODE B ,USER_RECORD C ");
        sqlStat.append("WHERE  A.MESSAGE_CODE_ID = B.ID AND A.ACTION_TAKER_ID=C.ID AND A.RECORD_STATUS = ? ");
        if (!Utility.isEmpty(eventMessageForm.getUserRecordID())) {
          sqlStat.append("AND A.CREATOR_ID = ? ");
        }
        if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          sqlStat.append("AND (A.UPDATE_DATE >= ? AND A.UPDATE_DATE <= ? ) ");
        }
        else if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          sqlStat.append("AND (A.UPDATE_DATE >= ? ) ");
        }
        else if (Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          sqlStat.append("AND (A.UPDATE_DATE <= ?  ) ");
        }

        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }

        sqlStat = this.getFormattedSQL(sqlStat.toString());

        // Form the ORDER clause for sorting.
        sqlStat.append("ORDER BY A.CREATE_DATE DESC  ");
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append(", " + sortAttribute + " " + searchForm.getSortOrder());
        }

        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        int i = 0;
        this.setPrepareStatement(preStat,++i,GlobalConstant.RECORD_STATUS_ACTIVE);
        if (!Utility.isEmpty(eventMessageForm.getUserRecordID())) {
          this.setPrepareStatement(preStat,++i,eventMessageForm.getUserRecordID());
        } 
        if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateFrom());
          this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateTo());
        }
        else if (!Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateFrom());
        }
        else if (Utility.isEmpty(eventMessageForm.getAccessDateFrom()) && !Utility.isEmpty(eventMessageForm.getAccessDateTo())) {
          this.setPrepareStatement(preStat, ++i, eventMessageForm.getAccessDateTo());
        }

        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, ++i, searchKeyword);
        }
        rs = preStat.executeQuery();

        while (rs.next()) {
          MtmEventMessageLog mtmEventMessageLog = new MtmEventMessageLog();        
          mtmEventMessageLog.setID(this.getResultSetInteger(rs,"ID"));
          mtmEventMessageLog.setMessageCodeID(this.getResultSetInteger(rs,"MESSAGE_CODE_ID"));
          mtmEventMessageLog.setMessage(this.getResultSetString(rs,"MESSAGE"));
          mtmEventMessageLog.setActionTakerID(this.getResultSetInteger(rs,"ACTION_TAKER_ID"));
          mtmEventMessageLog.setServerName(this.getResultSetString(rs,"SERVER_NAME"));
          mtmEventMessageLog.setCreatorID(this.getResultSetInteger(rs,"CREATOR_ID"));
          mtmEventMessageLog.setCreateDate(this.getResultSetTimestamp(rs,"CREATE_DATE"));
          mtmEventMessageLog.setRecordStatus(this.getResultSetString(rs,"RECORD_STATUS"));
          mtmEventMessageLog.setUpdateCount(this.getResultSetInteger(rs,"A.UPDATE_COUNT"));
          mtmEventMessageLog.setUpdaterID(this.getResultSetInteger(rs,"A.UPDATER_ID"));
          mtmEventMessageLog.setUpdateDate(this.getResultSetTimestamp(rs,"A.UPDATE_DATE"));
          mtmEventMessageLog.setEventType(this.getResultSetString(rs,"EVENT_TYPE"));
          mtmEventMessageLog.setMessagePattern(this.getResultSetString(rs,"MESSAGE_PATTERN"));
          mtmEventMessageLog.setMsg_Code(this.getResultSetString(rs,"ERROR_LABEL_KEY"));
          mtmEventMessageLog.setMessageCode(this.getResultSetString(rs,"MESSAGE_CODE"));
          mtmEventMessageLog.setSourceFunctionCode(this.getResultSetString(rs,"A.SOURCE_FUNCTION_CODE"));
          mtmEventMessageLog.setActionTakerName(this.getResultSetString(rs,"C.LOGIN_NAME"));
          mtmEventMessageLog.setErrorCode(this.getResultSetString(rs,"B.MESSAGE_CODE"));
          mtmEventMessageLog.setErrorDetail(this.getResultSetString(rs,"ERROR_DETAIL"));
          TextUtility.formatDate(mtmEventMessageLog.getUpdateDate(),CalendarUtility.TIME_FORMFAT_A);
          mtmEventMessageLog.setDate(TextUtility.formatDate(mtmEventMessageLog.getUpdateDate(),"yyyy-MM-dd"));
          mtmEventMessageLog.setTime(TextUtility.formatDate(mtmEventMessageLog.getUpdateDate(),"hh:mm:ss a"));
          mtmEventMessageLog.setActionTakerName(UserInfoFactory.getUserFullName(mtmEventMessageLog.getCreatorID()));
          result.add(mtmEventMessageLog);
        }

        return (result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try {
          rs.close();
        } catch (Exception ignore) {} finally {
          rs = null;
        }
        try {
          preStat.close();
        } catch (Exception ignore) {} finally {
          preStat = null;
        }
      }
    }
  }
}
